import 'package:floor/floor.dart';
import 'package:share_fit/app/dao/dao_mixin.dart';
import 'package:share_fit/app/models/home/sleep_model.dart';

@dao
abstract class SleepDao with DaoMixin<SleepModel> {
  @override
  @Query('SELECT * FROM sleep WHERE _id = :id')
  Stream<SleepModel?> findById(int id);

  @override
  @Query('SELECT * FROM sleep')
  Future<List<SleepModel>> findAll();

  @Query("SELECT "
      "calendar_time, MIN(start_time) as start_time, MAX(end_time) as end_time, SUM(sleep_time) as sleep_time, "
      "CASE "
      "WHEN is_snooze = 1 THEN 5 "
      "ELSE sleep_type "
      "END AS sleep_type "
      "from sleep where address = :address and calendar_time >= :startDate and calendar_time <= :endDate and sleep_type not in (7,8) "
      "GROUP BY sleep_type, calendar_time ORDER BY calendar_time")
  Future<List<SleepModel>> findTodayData(String address, String startDate, String endDate);

  @Query("SELECT start_time, end_time, sleep_time, sleep_type "
      "from ("
        "SELECT start_time, end_time, sleep_time, "
        "CASE "
        "WHEN is_snooze = 1 THEN 5 "
        "ELSE sleep_type "
        "END AS sleep_type "
        "from sleep "
        "where address = :address and calendar_time >= :startDate and calendar_time <= :endDate "
        "ORDER BY calendar_time) t "
      "where sleep_type not in (3,7,8) ")
  Future<List<SleepModel>> findInRangeData(String address, String startDate, String endDate);

  @Query("SELECT calendar_time, MIN(start_time) as start_time, MAX(end_time) as end_time,  SUM(sleep_time) as sleep_time, sleep_type "
      "from ("
        "SELECT calendar_time, start_time, end_time, sleep_time,"
        "CASE "
        "WHEN is_snooze = 1 THEN 5 "
        "ELSE sleep_type "
        "END AS sleep_type "
        "from sleep "
        "where address = :address and calendar_time >= :startDate and calendar_time <= :endDate) t "
      "where sleep_type not in (5,7,8) "
      "GROUP BY sleep_type, calendar_time ORDER BY calendar_time")
  Future<List<SleepModel>> findForBarChart(String address, String startDate, String endDate);

  @Query("SELECT MIN(start_time) as start_time, MAX(end_time) as end_time,  SUM(sleep_time) as sleep_time, sleep_type "
      "from ("
        "SELECT calendar, calendar_time, start_time, end_time, sleep_time,"
          "CASE "
          "WHEN is_snooze = 1 THEN 5 "
          "ELSE sleep_type "
          "END AS sleep_type "
        "from sleep "
        "where address = :address and calendar_time >= :startDate and calendar_time <= :endDate) t "
      "where sleep_type = 5 "
      "GROUP BY sleep_type, calendar ORDER BY calendar_time")
  Future<List<SleepModel>> findNaps(String address, String startDate, String endDate);

  @Query('SELECT sum(sleep_time) as sleep_time FROM sleep where address = :address and calendar = :calendar')
  Future<SleepModel?> findSleepTimeSum(String address, String calendar);

  @Query('select * from sleep where address = :address and calendar >= :calenderStart and calendar <= :calendarEnd')
  Future<List<SleepModel>> findForReport(String address, String calenderStart, String calendarEnd);

  /// 根据日期条件获取24小时内平均睡眠记录
  @Query("SELECT "
      "CAST(SUM(sleep_time) AS INT) as sleep_time, STRFTIME('%H:%S', calendar_time) as calendar, sleep_type "
      "FROM sleep WHERE calendar = :calendar and address = :address GROUP BY strftime('%H', calendar_time) order by calendar")
  Future<List<SleepModel>> findAvg(String address, String calendar);

  @override
  @Query('SELECT * FROM sleep')
  Stream<List<SleepModel>> findAllAsStream();

  @override
  @Query('delete FROM sleep')
  Future<void> deleteAll();

  @Query('SELECT * FROM sleep WHERE date_created >= :startTime and date_created < :endTime')
  Future<List<SleepModel>> findBy(int startTime, int endTime);

  /// 查找指定月份中有睡眠数据的日期
  @Query("select distinct calendar from sleep "
      "where address = :address and STRFTIME('%Y', calendar_time) >= :minYear and STRFTIME('%Y', calendar_time) <= :maxYear")
  Future<List<String>> findHasRecordDays(String address, String minYear, String maxYear);

  @Query('delete FROM sleep where address = :address')
  @override
  Future<void> deleteAllByAddress(String address);
}
